<?php

/**
 * @author ducnhat
 * @email ddnhat@gmail.com
 * @copyright 2014
 */

class Admin_Model_Liabilities extends Zend_Db_Table{
	
	public function getDept($customer_id = null, $year = null){
        $db = Zend_Registry::get('db');
        $db->setFetchMode(Zend_Db::FETCH_OBJ);
        
        $select = $db->select()
                    ->from(array('d' => 'dept'), array('SUM(dept) AS dept'))
                    ->joinRight(array('c' => 'customers'), "c.id = d.customer_id", array('name', 'id as customer_id'));
        
        if($year != null){
            $select->where("YEAR(d.date) = ?", $year);
        }
        
        if($customer_id != null){
            $select->where("d.customer_id = ?", $customer_id);
            $result = $db->fetchRow($select);
        }else{
            $select->group("c.id")
                    ->order("c.name");
            $result = $db->fetchAll($select);
        }
        
        return $result;
    }
    
    public function getPayment($customer_id = null){
    	$db = Zend_Registry::get('db');
        $db->setFetchMode(Zend_Db::FETCH_OBJ);
        
        $select = $db->select()
                    ->from(array('p' => 'payment'), array('SUM(total) AS total'))
                    ->joinRight(array('c' => 'customers'), "c.id = p.customer_id", array('name', 'id as customer_id'));
        
        if($customer_id != null){
            $select->where("p.customer_id = ?", $customer_id);
            $result = $db->fetchRow($select);
        }else{
            $select->group("c.id")
                    ->order("c.name");
            $result = $db->fetchAll($select);
        }
        
        return $result;
    }
    
    public function getOrders($customer_id = null, $date = null){
    	$db = Zend_Registry::get('db');
        $db->setFetchMode(Zend_Db::FETCH_OBJ);
        
        $select = $db->select()
                    ->from(array('o' => 'orders'), array('SUM(subtotal) AS subtotal', 'SUM(totalpay) AS totalpay'))
                    ->joinRight(array('c' => 'customers'), "c.id = o.customer_id", array('name', 'id as customer_id'));
        
        if($date != null){
            $select->where('o.date <= ?', $date);
        }
        
        if($customer_id != null){
            $select->where("o.customer_id = ?", $customer_id);
            $result = $db->fetchRow($select);
        }else{
            $select->group("c.id")
                    ->order("c.name");
            $result = $db->fetchAll($select);
        }
        
        return $result;
    }
    
    public function getCustomerTotal($customer_id = null){
    	$db = Zend_Registry::get('db');
        $db->setFetchMode(Zend_Db::FETCH_OBJ);
        
        $select = $db->select()
                    ->from(array('ct' => 'customer_total'), array('SUM(total) AS total'))
                    ->joinRight(array('c' => 'customers'), "c.id = ct.customer_id", array('name', 'id as customer_id'))
                    ->group("c.id")
                    ->order("c.name");
        
        if($customer_id == null){
            $result = $db->fetchAll($select);
        }else{
            $select->where('ct.customer_id = ?', $customer_id, 'INTEGER');
            
            $result = $db->fetchAll($select);
        }
        
        return $result;
    }
    
    public function getRefund($customer_id = null, $date = null){
    	$db = Zend_Registry::get('db');
        $db->setFetchMode(Zend_Db::FETCH_OBJ);
        
        $select = $db->select()
                    ->from(array('r' => 'refund'), array('SUM(total) AS total'))
                    ->joinRight(array('c' => 'customers'), "c.id = r.customer_id", array('name', 'id as customer_id'));
        
        if($date != null){
            $select->where("r.date <= ?", $date);
        }
        
        if($customer_id != null){
            $select->where("r.customer_id = ?", $customer_id);
            $result = $db->fetchRow($select);
        }else{
            $select->group("c.id")
                    ->order("c.name");
            $result = $db->fetchAll($select);
        }
        
        return $result;
    }
}